package com.apes.scm.masterdata.product.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.product.model.Product;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

/**
 * @author xul
 * @create 2018-02-06 16:47
 */
public interface ProductRepository extends CustomRepository<Product, String> {

    @Query("select T from Product T where id like %:value% or name like %:value%")
    Page<Product> find(@Param("value") String value, Pageable pageable);

    Product findFirstByOrginID(String orginId);

    @Query("select T from Product T where id in :value")
    List<Product> findByIdList(@Param("value") List value);

    @Query("select T from Product T where id in :value")
    List<Map> findByIdMapList(@Param("value") List value);

    @Query("select p from Product p where p.id like %:productId% or p.barcode like %:productId% or p.name like %:productId%")
    Page<Product> findByIdOrBarcodeOrName(@Param("productId") String productId, Pageable pageable);

    @Query("select p from Product p where p.id = :id or p.barcode like %:productId% ")
    Page<Product> findByIdOrBarcodeForScan(@Param("id") String id, @Param("productId") String productId, Pageable pageable);

    @Query(value = "select * from mas_product where id in ?1 AND free_supplies_valid = ?2", nativeQuery = true)
    List<Product> queryConsumableProduct(@Param("products") List products, @Param("freeSuppliesValid") int freeSuppliesValid);

    @Query(value ="SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME='MAS_SPXX'", nativeQuery = true)
    Page<Map> findClassifyComment(Pageable pageable);

    @Query("select T from Product T where id in :products")
    Page<Product> findByIdListPage(@Param("products") List products, Pageable pageable);

    @Query(value = "SELECT P.ID,P.NAME,P.BRAND_ID,P.CATEGORY_ID,P.OEM_ID,P.STATE,\n" +
            "(SELECT B.NAME FROM MAS_BRAND B WHERE P.BRAND_ID = B.ID) BRAND_NAME, \n" +
            "(SELECT C.NAME FROM MAS_CATEGORY C WHERE P.CATEGORY_ID = C.ID) CATEGORY_NAME FROM MAS_PRODUCT P WHERE P.CATEGORY_ID = :allCategoryId", nativeQuery = true)
    List<Map<String, String>> findAllByCategory(@Param("allCategoryId") String allCategoryId);

    List<Product> findAllByIdGreaterThanOrderByIdAsc(String id, Pageable pageable);

    @Query(value = "SELECT A.DSFSPID FROM YYERP.MAS_SPXX A WHERE A.SPMC LIKE :name AND A.DSFSPID LIKE 'M%' " +
            " UNION " +
            "SELECT A.DSFSPID FROM YYERP.MAS_SPXX A WHERE A.SPID LIKE :name AND A.DSFSPID LIKE 'M%' ORDER BY DSFSPID", nativeQuery = true)
    List<String> findByThirdParty(String name);

    @Query(value = "SELECT A.DSFSPID FROM TESTERP.MAS_SPXX A WHERE A.SPMC LIKE :name AND A.DSFSPID LIKE 'M%' " +
            " UNION " +
            "SELECT A.DSFSPID FROM TESTERP.MAS_SPXX A WHERE A.SPID LIKE :name AND A.DSFSPID LIKE 'M%' ORDER BY DSFSPID", nativeQuery = true)
    List<String> findByThirdPartyTest(String name);

    @Query(value = "SELECT A.* FROM MAS_PRODUCT A join YYERP.MAS_SPXX B on A.ID = B.SPID WHERE B.DSFSPID = :id", nativeQuery = true)
    Product findByThirdPartyId(String id);

    @Query(value = "SELECT A.* FROM MAS_PRODUCT A join TESTERP.MAS_SPXX B on A.ID = B.SPID WHERE B.DSFSPID = :id", nativeQuery = true)
    Product findByThirdPartyIdTest(String id);

    @Query(value = "SELECT DISTINCT A.MODEL name FROM MAS_PRODUCT A WHERE REGEXP_LIKE(MODEL, :model, 'i') AND A.CLASSIFY_ID = 'M01010102001'", nativeQuery = true)
    List<Map> findLikeModel(String model);

    @Query(value = "select a.id as id from Product a")
    List<String> findAllId();
}
